#coding=utf-8
#from uliweb import expose
#from sqlalchemy import *
#from uliweb.orm import *
#from uliweb.utils.pyini import Ini
#from sqlalchemy.sql import select
#from sqlalchemy.sql import and_, or_, not_
#
#import time, sys
#sys.path.insert(0, '../apps')
#from departments.models import *
#from charts.models import *
##from systems.models import *
##from requirements.models import *
##from charts.models import *
#
#import datetime
#from datetime import date
from get_month_quarter import *
#db = get_connection('sqlite:///../database.db', debug=False)
#调用get_month_quarter.py中get_db_connection()获取数据库联接
db = get_db_connection()

#meta = MetaData(db)
#db.metadata.reflect()

def run():
    
    #准开发需求相关报表数据处理
    req = db.metadata.tables['requirement']  
    #从requirement_kfzx_kfzxs表获取kfzx_id
    #使用以下方式连接requirement_kfzx_kfzxs表
    #req_system = db.metadata.tables['requirement_system_systems']

    req_sys = db.metadata.tables['system']      
                          
    #获取本月月初和月末时间
    month_first_day,month_last_day,month_view = get_month()
    

    #计算本月新增准开发需求任务数，算法：开始日期小于或等于本月末and大于等于本月1日
    #new = select([req_sys_obj.c.sys_id,func.count(req.c.req_id)],and_(req.c.created_date>=month_first_day,req.c.created_date<=month_last_day,req.c.req_id == req_system.c.requirement_id,req_sys_obj.c.id = req_system.c.system_id)).group_by(req_system.c.system_id)
    #old = select([req.c.tx,func.count(req.c.req_id)],and_(req.c.created_date<month_first_day,or_(req.c.stage!='17',req.c.stage!='18'))).group_by(req.c.tx)
    #续建准开发需求任务计算算法：排除修改日期大于本月1日且状态为完成或异常终止的记录
    #即：（创建日期<小于本月1日 and 修改日期小于小于本月1日 and 状态不为完
    #成或异常终止）or （创建日期<小于本月1日 and  修改日期大于等于本月1日小于本月月末日期）)
    #old = select([req_sys_obj.c.sys_id,func.count(req.c.req_id)],or_(and_(req.c.created_date<month_first_day,req.c.modified_date<month_first_day,~req.c.stage.in_(['17','18']),req.c.req_id == req_system.c.requirement_id,req_sys_obj.c.id = req_system.c.system_id),and_(req.c.created_date<month_first_day,req.c.modified_date<month_last_day,req.c.modified_date>=month_first_day,req.c.req_id == req_system.c.requirement_id,req_sys_obj.c.id = req_system.c.system_id))).group_by(req_system.c.system_id)
    num = select([req_sys.c.sys_id,func.count(req.c.req_id),req_sys.c.kfzx],
                  or_(and_( req.c.modified_date<month_first_day,
                            ~req.c.stage.in_(['19','1A','FN']),
                            req.c.main_sys == req_sys.c.id
                           ),
                        and_(req.c.modified_date<=month_last_day,
                             req.c.modified_date>=month_first_day,
                             req.c.main_sys == req_sys.c.id
                             )
                        )
                    ).group_by(req_sys.c.sys_id)
    #print num
    #new_rs = new.execute()
    #old_rs = old.execute()
    #将本月准开发需求汇总数据赋值给total_num
    #print "num",num
    
    req_model = get_model('requirement_month_report_system')

    #插入数据前，先删除本月已有数据，该语句可以保证该脚本每个月多次跑批
    #db.execute(req_model.delete().where(req_model.c.month_date == month_last_day)) 
    req_model.remove(req_model.c.month_date == month_last_day)
    #print data

    num_rs = num.execute()

    #说明，在未使用list之前，num_rs是每取一条记录访问一次数据库，就像游标
    for row in list(num_rs):
        data = {}
     #   data['num'] = 0
        sys_id,count,kfzx = row
    #    print sys_id,count
        data['system_id'] = sys_id
        data['num'] = count
        data['month_date'] = month_last_day
        data['month'] = month_view
        data['kfzx'] = kfzx
        #print 'data',data 
        r = req_model(**data)
        r.save()

if __name__ == '__main__':
    try:
        db.begin()
        run()
        db.commit()
        log.info("finish")
    except Exception, e:
        db.rollback()
        log.exception(e)
        log.error('error')
        raise 

